#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QString>
#include <QLineEdit>
#include <QStandardItemModel>
#include <QStandardItem>
#include <QMessageBox>

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);


    dbSql = QSqlDatabase::addDatabase("QMYSQL");
    dbSql.setDatabaseName("simple");
    dbSql.setHostName("localhost");
    dbSql.setPort(3306);
    dbSql.setUserName("root");
    dbSql.setPassword("189188");
    if(!dbSql.open())
    {
        ui->status->setText("failed to connect to database");
    }
    else
    {
        ui->status->setText("connected to mysql");
    }
}

MainWindow::~MainWindow()
{
    dbSql.close();
    delete ui;
}

void MainWindow::on_AddData_clicked()
{

        QSqlQuery query;
        QString state = "insert into student values ("+(ui->myID->text())+",'"+ui->myName->text()+"','"+ui->mySex->text()+"');";
        query.exec(state);
        ui->status->setText("inserted "+(ui->myID->text())+",'"+ui->myName->text()+"','"+ui->mySex->text()+"' into student.");
        ui->myID->text().clear();
        ui->myName->text().clear();
        ui->mySex->text().clear();
}

void MainWindow::on_UpdateData_clicked()
{
    QSqlQuery query;
    QString state;
    if(ui->myID->text().trimmed()=="")
    {
        QMessageBox *message = new QMessageBox;
        message->setText("ID could not be blank!!");
        message->show();
        return;
    }
    if(ui->myName->text().trimmed()!=""&&ui->mySex->text().trimmed()!="")
    {
        state = "update student set name = '"+ui->myName->text().trimmed()+"'  sex='"
                +ui->mySex->text().trimmed()+"' where id = "+ui->myID->text().trimmed()+";";
    }
    else if (ui->myName->text().trimmed()!=""&&ui->mySex->text().trimmed()=="")
    {
        state = "update student set name = '"+ui->myName->text().trimmed()+
                "' where id = "+ui->myID->text().trimmed()+";";
    }
    else if (ui->myName->text().trimmed()==""&&ui->mySex->text().trimmed()!="")
    {
        state = "update student set id = '"+ui->mySex->text().trimmed()+
                "' where id = "+ui->myID->text().trimmed()+";";
    }
    query.exec(state);
    ui->status->setText(state);
}

void MainWindow::on_SearchData_clicked()
{
    QSqlQuery query;
     QString state;
    bool bID = false, bName = false, bSex = false;
    if(ui->myID->text().trimmed()!="")
    {
        bID = true;
    }
    if(ui->myName->text().trimmed()!="")
    {
        bName = true;
    }
    if(ui->mySex->text().trimmed()!="")
    {
        bSex = true;
    }
    if(bID&&bName&&bSex)
    {
        state = "select * from student where id = "+(ui->myID->text().trimmed())+" and name = '"
                +ui->myName->text().trimmed()+"' and sex = '"
                +ui->mySex->text().trimmed()+"';";
    }
    if(bID&&bName&&!bSex)
    {
        state = "select * from student where id = "+(ui->myID->text().trimmed())+" and name = '"
                +ui->myName->text().trimmed()+"';";
    }
    if(bID&&bSex&&!bName)
    {
        state = "select * from student where id = "+(ui->myID->text().trimmed())+" and sex = '"
                +ui->mySex->text().trimmed()+"';";
    }
    if(bName&&bSex&&!bID)
    {
        state = "select * from student where name = '"+(ui->myName->text().trimmed())+"' and sex = '"
                +ui->mySex->text().trimmed()+"';";
    }
    if(bID&&!bName&&!bSex)
    {
        state = "select * from student where id = "+(ui->myID->text().trimmed())+";";
    }
    if(bName&&!bID&&!bSex)
    {
        state = "select * from student where name = '"+(ui->myName->text().trimmed())+"';";
    }
    if(bSex&&!bID&&!bName)
    {
        state = "select * from student where sex = '"+(ui->mySex->text().trimmed())+"';";
    }
    if(!bID&&!bName&&!bSex)
    {
         state = "select * from student ;";
    }
    ui->status->setText(state);
    query.exec(state);
    QStandardItemModel *model = new QStandardItemModel;
    model->setColumnCount(3);
    model->setHeaderData(0,Qt::Horizontal,tr("ID"));
    model->setHeaderData(1,Qt::Horizontal,tr("Name"));
    model->setHeaderData(2,Qt::Horizontal,tr("Sex"));
    int i = 0;
    while(query.next())
    {
        model->setItem(i,0,new QStandardItem(query.value(0).toString()));
        model->setItem(i,1,new QStandardItem(query.value(1).toString()));
        model->setItem(i,2,new QStandardItem(query.value(2).toString()));
        i++;
    }
    ui->Result->setModel(model);
}

void MainWindow::on_DeleteData_clicked()
{
    QSqlQuery query;
    if(ui->myID->text().trimmed()=="")
    {
        QMessageBox *message = new QMessageBox;
        message->setText("Please input the ID of the data that you want to delete!");
        message->show();
        return;
    }
    QString state = "delete from student where id = "+ui->myID->text().trimmed()+";";
    query.exec(state);
}
